package JDBC;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;
public class JDBC_test {
	//获取连接对象方法
		public static Connection getCon() {
			 Connection conn =null;
			 try{
				  Class.forName("com.mysql.cj.jdbc.Driver");
				  String user="spring-3";
			 	  String url="jdbc:mysql://txyx.feng4yu.cn:3306/spring-3?useSSL=false";
			 	  String password="12345678";
			 	    
			 	  conn = DriverManager.getConnection(url, user, password);
			  }catch(ClassNotFoundException e){
				  e.printStackTrace();
			  }catch(SQLException e) {
				  e.printStackTrace();
			  }catch(Exception e) {
				  e.printStackTrace();
			  }
			return conn;
		}
		
		
		@Test
		public void test_JDBC() {
			Connection conn = null;
	        Statement stmt = null;
	        ResultSet rs = null;
	        try{
	            // 注册 JDBC 驱动
	            
	        
	            // 打开链接
	            System.out.println("连接数据库...");
	            conn = JDBC_test.getCon();
	        
	            // 执行查询
	            System.out.println(" 实例化Statement对象...");
	            stmt = conn.createStatement();
	            
	            stmt.execute("drop table if exists account;");
	            stmt.execute("create table account("
	 				   +     "id int primary key auto_increment,"
					   +     "username varchar(50) ,"
					   + 	 "balance double)");
	            
	            stmt.execute("insert into account(username,balance) value('Joy_test',100.0);");
	            stmt.execute("insert into account(username,balance) value('Tom_test',1000.0);");
	            stmt.execute("insert into account(username,balance) value('Jack_test',2000.0);");
	            stmt.execute("insert into account(username,balance) value('Rose_test',500.0);");
	            
	            String sql;
	            sql = "SELECT * FROM account";
	             rs = stmt.executeQuery(sql);
	        
	            // 展开结果集数据库
	            while(rs.next()){
	                // 通过字段检索
	                int id  = rs.getInt("id");
	                String username = rs.getString("username");
	                String balance = rs.getString("balance");
	    
	                // 输出数据
	                System.out.print("ID: " + id);
	                System.out.print(", 用户名: " + username);
	                System.out.print(", 余额: " + balance);
	                System.out.print("\n");
	            }
	           
	        }catch(SQLException se){
	            // 处理 JDBC 错误
	            se.printStackTrace();
	        }catch(Exception e){
	            // 处理 Class.forName 错误
	            e.printStackTrace();
	        }finally{
	        	 // 完成后关闭
	            JDBC_test.realse(rs, stmt, conn);
	        }
	        System.out.println("Goodbye!");
	    }
	
		
		//关闭连接
		public static void realse(ResultSet rs,Statement st, Connection conn) {
			if(rs!=null){
	 			try{
	 				rs.close();		
	 			}catch(SQLException e){
	 				e.printStackTrace();
	 			}
	 		}
	 		if(st!=null){
	 			try{
	 				st.close();	
	 			}catch(SQLException e){
	 				e.printStackTrace();
	 			}
	 		}
	 		if(conn!=null){
	 			try{
	 			  	conn.close();	
	 			}catch(SQLException e){
	 				e.printStackTrace();
	 			}
	 		}
		}
}
